package com.newway.wechat.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.newway.wechat.entity.MaterialInfo;
import com.newway.wechat.entity.MaterialRecord;
import com.newway.wechat.mapper.MaterialInfoMapper;
import com.newway.wechat.mapper.MaterialRecordMapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelService {


    @Autowired
    MaterialRecordMapper materialRecordMapper;

    @Autowired
    MaterialInfoMapper materialInfoMapper;


    public ResponseEntity<ByteArrayResource> downloadExcelTemplate(String id) throws IOException {
        try{
            List<MaterialInfo> list = new ArrayList<>();
            if(StringUtils.isBlank(id)){
                list = materialInfoMapper.selectList(null);
            }else{
                MaterialInfo materialInfo = materialInfoMapper.selectById(id);
                if(materialInfo!=null){
                    list.add(materialInfo);
                }
            }
            //文件读取模板
        /*Resource resource = new ClassPathResource("templates/records.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(resource.getInputStream());*/
            //手动创建
            XSSFWorkbook workbook = new XSSFWorkbook();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //设置字体为加粗
            Font font = workbook.createFont();
            font.setBold(true);
            // 创建单元格样式
            CellStyle style = workbook.createCellStyle();
            style.setFont(font);
            for(int i=0;i<list.size();i++){
                MaterialInfo materialInfo = list.get(i);
                QueryWrapper<MaterialRecord> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("material_id",materialInfo.getMaterialId());
                List<MaterialRecord> records = materialRecordMapper.selectList(queryWrapper);
                //使用空白模板Excel时候前三个处理一下
            /*Sheet sheet;
            if(i<=2){
                sheet = workbook.getSheetAt(i);
                workbook.setSheetName(i,materialInfo.getMaterialId());
            }else{
                sheet = workbook.createSheet(materialInfo.getMaterialId());
            }*/
                Sheet sheet = workbook.createSheet(materialInfo.getMaterialId());
                sheet.createRow(0);
                //合并对应单元格（第一行的2和3列）
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
                //合并对应单元格（第一行的5和6列）
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));
                sheet.createRow(1);
                //合并对应单元格（第二行的2和3列）
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
                //合并对应单元格（第二行的5和6列）
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
                //顶头
                Cell idCell = sheet.getRow(0).createCell(0);
                idCell.setCellStyle(style);
                idCell.setCellValue("材料编号");
                sheet.getRow(0).createCell(1).setCellValue(materialInfo.getMaterialId());
                Cell name = sheet.getRow(0).createCell(3);
                name.setCellValue("材料名称");
                name.setCellStyle(style);
                sheet.getRow(0).createCell(4).setCellValue(materialInfo.getMaterialName());
                Cell unit = sheet.getRow(1).createCell(0);
                unit.setCellStyle(style);
                unit.setCellValue("计量单位");
                sheet.getRow(1).createCell(1).setCellValue(materialInfo.getUnit());
                Cell specification = sheet.getRow(1).createCell(3);
                specification.setCellValue("规格");
                specification.setCellStyle(style);
                sheet.getRow(1).createCell(4).setCellValue(materialInfo.getSpecification());
                //数据头
                sheet.createRow(2);
                sheet.getRow(2).createCell(0);
                Cell timeCell = sheet.getRow(2).getCell(0);
                timeCell.setCellStyle(style);
                timeCell.setCellValue("时间");
                Cell summary = sheet.getRow(2).createCell(1);
                summary.setCellValue("摘要");
                summary.setCellStyle(style);
                Cell inData = sheet.getRow(2).createCell(2);
                inData.setCellStyle(style);
                inData.setCellValue("收入数");
                Cell outData = sheet.getRow(2).createCell(3);
                outData.setCellValue("发出数");
                outData.setCellStyle(style);
                Cell balance = sheet.getRow(2).createCell(4);
                balance.setCellStyle(style);
                balance.setCellValue("结存数");
                Cell operator = sheet.getRow(2).createCell(5);
                operator.setCellValue("经手人签字");
                operator.setCellStyle(style);
                int rowNum = 3; // 假设表头在第一行，从第二行开始填充数据
                for (MaterialRecord record : records) {
                    Row dataRow = sheet.createRow(rowNum++);
                    dataRow.createCell(0).setCellValue(record.getOperationTime());
                    dataRow.createCell(1).setCellValue(record.getRemark());
                    dataRow.createCell(2).setCellValue(record.getIncomeQuantity());
                    dataRow.createCell(3).setCellValue(record.getOutgoingQuantity());
                    dataRow.createCell(4).setCellValue(record.getBalanceQuantity());
                    dataRow.createCell(5).setCellValue(record.getOperatorName());
                }
            }
            workbook.write(bos);
            return ResponseEntity.ok()
                    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=details.xlsx")
                    .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
                    .body(new ByteArrayResource(bos.toByteArray()));
        }catch (Exception e){
            Resource resource = new ClassPathResource("templates/records.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(resource.getInputStream());
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            return ResponseEntity.ok()
                    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=details.xlsx")
                    .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
                    .body(new ByteArrayResource(bos.toByteArray()));
        }

    }
}
